package com.newegg.hackathon.arangodb.dao;

import java.util.List;
import com.arangodb.springframework.annotation.Query;
import com.arangodb.springframework.annotation.QueryOptions;
import com.newegg.hackathon.arangodb.BasicDao;
import com.newegg.hackathon.arangodb.model.Item;

public interface ItemDao extends BasicDao<Item>{

	default Item create(String id, String name, String url, String image, String brand_id, String category_id) {
		Item item = new Item();
		item.setId(BasicDao.id(id));
		item.setName(name);
		item.setItemnumber(id);
		item.setUrl(url);
		String short_name = name.split(",")[0];
		if (short_name.length() > 20) {
			short_name = short_name.substring(0, 20);
		}
		item.setShort_name(short_name);
		item.setImage(image);
		item.setBrand_id(brand_id);
		item.setCategory_id(category_id);
		return item;
	}

	default Item getOrCreate(String id, String name, String url, String image, String brand_id, String category_id) {
		Item item = get(id);
		if(item == null) {
			item = create(id, name, url, image, brand_id, category_id);
			save(item);
		}
		return item;
	}

	@Query("FOR i IN item SORT RAND() LIMIT 1 RETURN i")
	Item getOne();

	@Query("LET itemKey = @itemid\r\n" + 
			"LET category_id = @category\r\n" + 
			"FOR source_itemspec IN itemSpec\r\n" + 
			"    FILTER source_itemspec._from == itemKey\r\n" + 
			"    FOR target_item, target_itemspec IN 1..1 INBOUND source_itemspec._to itemSpec\r\n" + 
			"        FILTER target_itemspec._from != itemKey\r\n" + 
			"        FILTER target_item.category_id == category_id\r\n" + 
			"        COLLECT target_items = target_item,\r\n" + 
			"                category = target_item.category_id,\r\n" + 
			"                brand = target_item.brand_id\r\n" + 
			"        AGGREGATE target_length = LENGTH(target_itemspec)\r\n" + 
			"    FILTER target_length > 0\r\n" + 
			"    SORT CONCAT(target_length, brand, category) DESC\r\n" + 
			"    LIMIT @limit\r\n" + 
			"    return target_items")
	@QueryOptions(cache=true)
	List<Item> findCategoryNear(String itemid, String category, int limit);
	
	@Query("LET itemKey = @itemid\r\n" + 
			"LET category_id = @category\r\n" + 
			"FOR source_itemspec IN itemSpec\r\n" + 
			"    FILTER source_itemspec._from == itemKey\r\n" + 
			"    FOR target_item, target_itemspec IN 1..1 INBOUND source_itemspec._to itemSpec\r\n" + 
			"        FILTER target_itemspec._from != itemKey\r\n" + 
			"        FILTER target_item.category_id != category_id\r\n" + 
			"        COLLECT target_items = target_item,\r\n" + 
			"                category = target_item.category_id,\r\n" + 
			"                brand = target_item.brand_id\r\n" + 
			"        AGGREGATE target_length = LENGTH(target_itemspec)\r\n" + 
			"    FILTER target_length > 0\r\n" + 
			"    SORT CONCAT(target_length, brand, category) DESC\r\n" + 
			"    LIMIT @limit\r\n" + 
			"    return target_items")
	@QueryOptions(cache=true)
	List<Item> findNoCategoryNear(String itemid, String category, int limit);

	@Query("LET itemKey = @itemid\r\n" + 
			"LET brand_id = @brand\r\n" + 
			"FOR source_itemspec IN itemSpec\r\n" + 
			"    FILTER source_itemspec._from == itemKey\r\n" + 
			"    FOR target_item, target_itemspec IN 1..1 INBOUND source_itemspec._to itemSpec\r\n" + 
			"        FILTER target_itemspec._from != itemKey\r\n" + 
			"        FILTER target_item.brand_id == brand_id\r\n" + 
			"        COLLECT target_items = target_item,\r\n" + 
			"                category = target_item.category_id,\r\n" + 
			"                brand = target_item.brand_id\r\n" + 
			"        AGGREGATE target_length = LENGTH(target_itemspec)\r\n" + 
			"    FILTER target_length > 0\r\n" + 
			"    SORT CONCAT(target_length, category, brand) DESC\r\n" + 
			"    LIMIT @limit\r\n" + 
			"    return target_items")
	@QueryOptions(cache=true)
	List<Item> findBrandNear(String itemid, String brand, int limit);

	@Query("LET itemKey = @itemid\r\n" + 
			"LET brand_id = @brand\r\n" + 
			"FOR source_itemspec IN itemSpec\r\n" + 
			"    FILTER source_itemspec._from == itemKey\r\n" + 
			"    FOR target_item, target_itemspec IN 1..1 INBOUND source_itemspec._to itemSpec\r\n" + 
			"        FILTER target_itemspec._from != itemKey\r\n" + 
			"        FILTER target_item.brand_id != brand_id\r\n" + 
			"        COLLECT target_items = target_item,\r\n" + 
			"                brand = target_item.brand_id\r\n" + 
			"        AGGREGATE target_length = LENGTH(target_itemspec)\r\n" + 
			"    FILTER target_length > 0\r\n" + 
			"    SORT CONCAT(target_length, brand) DESC\r\n" + 
			"    LIMIT @limit\r\n" + 
			"    return target_items")
	@QueryOptions(cache=true)
	List<Item> findNoBrandNear(String itemid, String brand, int limit);

	@Query("FOR i IN item FILTER LOWER(i.name) LIKE CONCAT('%', LOWER(@keyword) , '%') OR LOWER(i.itemnumber) == LOWER(@keyword) LIMIT @limit RETURN i")
	@QueryOptions(cache=true)
	List<Item> findItems(String keyword, Integer limit);
	
	@Query("LET items = FLATTEN(\r\n" + 
			"    FOR prop IN @specs\r\n" + 
			"        FOR item, edge IN 1..1 INBOUND prop itemSpec\r\n" + 
			"        return DISTINCT item\r\n" + 
			")\r\n" + 
			"FOR item IN items\r\n" + 
			"    LET count = LENGTH(\r\n" + 
			"        FOR i, itemspec IN 1..1 OUTBOUND item._id itemSpec\r\n" + 
			"            FOR prop IN @specs\r\n" + 
			"            FILTER itemspec._to == prop\r\n" + 
			"            return 1\r\n" + 
			"    )\r\n" + 
			"    SORT count DESC\r\n" + 
			"    LIMIT @limit\r\n" + 
			"return item")
	List<Item> findItemBySpecs(List<String> specs, int limit);
}
